﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace QuanLyKhachHang.Forms
{
    #region using
    using QuanLyKhachHang.BUS;
    using QuanLyKhachHang.DTO;
    using QuanLyKhachHang.Utilities;
    using System.IO;
    using NPOI.HSSF.UserModel;
    using NPOI.HPSF;
    using NPOI.SS.UserModel;
    using NPOI.HSSF.UserModel.Contrib;
    using NPOI.HSSF.Util;
    using NPOI.SS.Util;
    #endregion

    public partial class frmBCHDTT1 : Form
    {
        private delegate void TimeTask();
        private frmWaiting waiting;
        private DataSet objDonViDS;
        private DataSet objCSUDDS;
        private DataSet objKhachHangDS;
        private DataTable dataTable = new DataTable();
        private string maDonVi;
        private int thang;
        private int nam;
        private HSSFWorkbook hssfworkbook;
        private DataTable dtDoanhThuTT = new DataTable();
        private DataTable dtDoanhThuCK = new DataTable();
        public frmBCHDTT1()
        {
            InitializeComponent();
            InitGridViewHienThi();
            InitTableDoanhThu(dtDoanhThuTT);
            InitTableDoanhThu(dtDoanhThuCK);
        }

        public void InitTableDoanhThu(DataTable dataTable)
        {
            string str = "";
            long i = 0;
            DataColumn col1 = new DataColumn("MACSUD", str.GetType());
            DataColumn col2 = new DataColumn("MAKH", i.GetType());
            DataColumn col3 = new DataColumn("TONGDOANHTHU", i.GetType());
            DataColumn col4 = new DataColumn("SOTIENCHI", i.GetType());

            dataTable.Columns.Add(col1);
            dataTable.Columns.Add(col2);
            dataTable.Columns.Add(col3);
            dataTable.Columns.Add(col4);
        }

        public void InitGridViewHienThi()
        {
            string str = "";
            long i = 0;
            DataColumn col1 = new DataColumn("STT", str.GetType());
            DataColumn col2 = new DataColumn("MUCDOANHTHU", str.GetType());
            DataColumn col3 = new DataColumn("SLKHTT", str.GetType());

            DataColumn col4 = new DataColumn("SLKHCK", str.GetType());
            DataColumn col5 = new DataColumn("TONGDOANHTHUTTCK", str.GetType());
            DataColumn col6 = new DataColumn("SOTIENTT", str.GetType());
            DataColumn col7 = new DataColumn("SOTIENCK", str.GetType());
            DataColumn col8 = new DataColumn("TONGTIENTTCK", str.GetType());
            DataColumn col9 = new DataColumn("TONGDOANHTHUTHANG", str.GetType());
            DataColumn col10 = new DataColumn("GHICHU", str.GetType());
            
            dataTable.Columns.Add(col1);
            dataTable.Columns.Add(col2);
            dataTable.Columns.Add(col3);
            dataTable.Columns.Add(col4);
            dataTable.Columns.Add(col5);
            dataTable.Columns.Add(col6);
            dataTable.Columns.Add(col7);
            dataTable.Columns.Add(col8);
            dataTable.Columns.Add(col9);
            dataTable.Columns.Add(col10);
        }

        public bool ValidateInput()
        {
            if (cbxDonVi.Text.Equals(""))
            {
                MessageBox.Show("Bạn chưa chọn đơn vị", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }

            if (txtThang.Text.Equals(""))
            {
                MessageBox.Show("Bạn chưa nhập tháng", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }

            if (txtNam.Text.Equals(""))
            {
                MessageBox.Show("Bạn chưa nhập năm", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }

            if (!Utils.IsNumeric(txtThang.Text))
            {
                MessageBox.Show("Tháng phải là số hợp lệ", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }
            else
            {
                if (int.Parse(txtThang.Text) < 1 || int.Parse(txtThang.Text) > 12)
                {
                    MessageBox.Show("Tháng phải từ 1-12 ", "Bao Loi",
                        MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    return false;
                }
            }

            if (!Utils.IsNumeric(txtNam.Text))
            {
                MessageBox.Show("Năm phải là số hợp lệ", "Bao Loi",
                    MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return false;
            }
            else
            {
                if (int.Parse(txtNam.Text) <= 0)
                {
                    MessageBox.Show("Năm phải từ > 0 ", "Bao Loi",
                        MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    return false;
                }
            }

            return true;
        }

        public void LoadDonVi()
        {
            var objBUS = new DonViBUS(GlobalVariables.g_strConnectionString);

            try
            {
                objDonViDS = objBUS.SelectAll();

                for (int i = 0; i < objDonViDS.Tables["DonVi"].Rows.Count; i++)
                {
                    DataRow row = objDonViDS.Tables["DonVi"].Rows[i];
                    cbxDonVi.Items.Add(new DonViDTO { MADONVI = row["MADONVI"].ToString(), TENDONVI = row["TENDONVI"].ToString() });
                }

                if (cbxDonVi.Items.Count > 0)
                    cbxDonVi.SelectedIndex = 0;
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public void LoadCSUD()
        {
            var objBUS = new CS_UUDAIBUS(GlobalVariables.g_strConnectionString);

            try
            {
                objCSUDDS = objBUS.SelectAll();
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public void LoadKhachHangTTCK(string madonvi, int thang, int nam)
        {
            var objBUS = new TrichThuong_ChietKhauBUS(GlobalVariables.g_strConnectionString);

            try
            {
                //select kh by madonvi, thang, nam
                objKhachHangDS = objBUS.SelectByDonViThangNam(maDonVi, thang, nam);
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        private void frmBCHDTT1_Load(object sender, EventArgs e)
        {
            LoadDonVi();
            LoadCSUD();
        }

        public string GetLoaiCSUDFromDoanhThuTT(long doanhthu)
        {
            string loai = "";
            for (int i = 0; i < objCSUDDS.Tables[0].Rows.Count; i++)
            {
                DataRow row = objCSUDDS.Tables[0].Rows[i];
                string type = row["LOAI"].ToString().Substring(0, 2);
                if (type == "TT")
                {
                    string[] comp = row["LOAI"].ToString().Substring(2).Trim().Split(new char[] { '-' });
                    long min = long.Parse(comp[0]) * 1000;
                    long max = min;
                    if(comp[comp.Length - 1] != "")
                        max = long.Parse(comp[comp.Length - 1]) * 1000;
                    if (doanhthu >= min && doanhthu <= max)
                    {
                        loai = row["LOAI"].ToString().Substring(2);
                        break;
                    }
                }
            }
            return loai;
        }

        public string GetLoaiCSUDFromDoanhThuCK(long doanhthu)
        {
            string loai = "";
            for (int i = 0; i < objCSUDDS.Tables[0].Rows.Count; i++)
            {
                DataRow row = objCSUDDS.Tables[0].Rows[i];
                string type = row["LOAI"].ToString().Substring(0, 2);
                if (type == "CK")
                {
                    string[] comp = row["LOAI"].ToString().Substring(2).Trim().Split(new char[] { '-' });
                    long min = long.Parse(comp[0]) * 1000;
                    long max = min;
                    if (comp[comp.Length - 1] != "")
                        max = long.Parse(comp[comp.Length - 1]) * 1000;
                    if (doanhthu >= min && doanhthu <= max)
                    {
                        loai = row["LOAI"].ToString().Substring(2);
                        break;
                    }
                }
            }
            return loai;
        }

        public List<string> GetMucDoanhThu()
        {
            var lstMucDoanhThu = new List<string>();
            for (int i = 0; i < objCSUDDS.Tables[0].Rows.Count; i++)
            {
                DataRow row = objCSUDDS.Tables[0].Rows[i];
                string type = row["LOAI"].ToString().Substring(0, 2);
                if (type == "TT")
                {
                    lstMucDoanhThu.Add(row["LOAI"].ToString().Trim().Substring(2));
                }
            }

            if (lstMucDoanhThu.Count > 1)
            {
                for (int i = 0; i < lstMucDoanhThu.Count - 1; i++)
                {
                    for (int j = i+1; j < lstMucDoanhThu.Count; j++)
                    {
                        string[] compi = lstMucDoanhThu[i].Trim().Split(new char[] { '-' });
                        long mini = long.Parse(compi[0]) * 1000;
                            

                        string[] compj = lstMucDoanhThu[j].Trim().Split(new char[] { '-' });
                        long minj = long.Parse(compj[0]) * 1000;
                            

                        if (mini > minj)
                        {
                            var t = lstMucDoanhThu[i];
                            lstMucDoanhThu[i] = lstMucDoanhThu[j];
                            lstMucDoanhThu[j] = t;
                        }
                    }
                }
            }

            return lstMucDoanhThu;
        }

        public void GetDoanhThuTTCK(ref DataTable dictTT, ref DataTable dictCK)
        {
            for (int i = 0; i < objKhachHangDS.Tables[0].Rows.Count; i++)
            {
                var row = objKhachHangDS.Tables[0].Rows[i];
                var doanhthutrichthuong = long.Parse(row["TONGTRICHTHUONG"].ToString());
                var chitrichthuong = long.Parse(row["CHITRICHTHUONG"].ToString());
                var doanhthuchietkhau = long.Parse(row["TONGCHIETKHAU"].ToString());
                var chichietkhau = long.Parse(row["CHICHIETKHAU"].ToString());
                if (doanhthutrichthuong > 0 || chitrichthuong > 0)
                {
                    var doanhthukh = new DoanhThuKhachHang
                    {
                        MaKH = int.Parse(row["MAKH"].ToString()),
                        TongDoanhThu = doanhthutrichthuong,
                        SoTienChi = chitrichthuong
                    };
                    var loaicsud = GetLoaiCSUDFromDoanhThuTT(doanhthukh.TongDoanhThu);

                    DataRow newrow = dictTT.Rows.Add();
                    newrow["MACSUD"] = loaicsud;
                    newrow["MAKH"] = doanhthukh.MaKH;
                    newrow["TONGDOANHTHU"] = doanhthukh.TongDoanhThu;
                    newrow["SOTIENCHI"] = doanhthukh.SoTienChi;
                }

                if (doanhthuchietkhau > 0 || chichietkhau > 0)
                {
                    var doanhthukh = new DoanhThuKhachHang
                    {
                        MaKH = int.Parse(row["MAKH"].ToString()),
                        TongDoanhThu = doanhthuchietkhau,
                        SoTienChi = chichietkhau
                    };
                    var loaicsud = GetLoaiCSUDFromDoanhThuCK(doanhthukh.TongDoanhThu);

                    DataRow newrow = dictCK.Rows.Add();
                    newrow["MACSUD"] = loaicsud;
                    newrow["MAKH"] = doanhthukh.MaKH;
                    newrow["TONGDOANHTHU"] = doanhthukh.TongDoanhThu;
                    newrow["SOTIENCHI"] = doanhthukh.SoTienChi;
                }
            }
        }

        public int GetSoLuongKH(DataTable dtDoanhThu, string loai)
        {
            int sl = 0;
            for (int i = 0; i < dtDoanhThu.Rows.Count; i++)
            {
                DataRow row = dtDoanhThu.Rows[i];
                if (row["MACSUD"].ToString() == loai)
                    sl++;
            }
            return sl;
        }

        public long GetTongDoanhThu(DataTable dtDoanhThu, string loai)
        {
            long tong = 0;
            for (int i = 0; i < dtDoanhThu.Rows.Count; i++)
            {
                DataRow row = dtDoanhThu.Rows[i];
                if (row["MACSUD"].ToString() == loai)
                    tong += long.Parse(row["TONGDOANHTHU"].ToString());
            }
            return tong;
        }

        public long GetSoTienChi(DataTable dtDoanhThu, string loai)
        {
            long tong = 0;
            for (int i = 0; i < dtDoanhThu.Rows.Count; i++)
            {
                DataRow row = dtDoanhThu.Rows[i];
                if (row["MACSUD"].ToString() == loai)
                    tong += long.Parse(row["SOTIENCHI"].ToString());
            }
            return tong;
        }

        private void btnXem_Click(object sender, EventArgs e)
        {
            if (ValidateInput())
            {
                thang = int.Parse(txtThang.Text);
                nam = int.Parse(txtNam.Text);
                maDonVi = ((DonViDTO)cbxDonVi.SelectedItem).MADONVI;

                LoadKhachHangTTCK(maDonVi, thang, nam);

                dataTable.Clear();
                dtDoanhThuTT.Clear();
                dtDoanhThuCK.Clear();

                dtgvDanhSachHienThi.DataSource = null;
                dtgvDanhSachHienThi.Columns.Clear();

                var lstMucDoanhThu = GetMucDoanhThu();
                GetDoanhThuTTCK(ref dtDoanhThuTT, ref dtDoanhThuCK);

                for (int i = 0; i < lstMucDoanhThu.Count; i++)
                {
                    DataRow row2;
                    dataTable.Rows.Add();
                    row2 = dataTable.Rows[i];

                    row2["STT"] = (i + 1).ToString();
                    if (i > 0)
                    {
                        row2["MUCDOANHTHU"] = "Trên " + lstMucDoanhThu[i];
                    }
                    else
                    {
                        row2["MUCDOANHTHU"] = "Từ " + lstMucDoanhThu[i];
                    }
                    row2["SLKHTT"] = GetSoLuongKH(dtDoanhThuTT, lstMucDoanhThu[i]);
                    row2["SLKHCK"] = GetSoLuongKH(dtDoanhThuCK, lstMucDoanhThu[i]);

                    row2["TONGDOANHTHUTTCK"] = string.Format("{0:#,##0;Nothing}", GetTongDoanhThu(dtDoanhThuTT, lstMucDoanhThu[i]) + GetTongDoanhThu(dtDoanhThuCK, lstMucDoanhThu[i]));

                    long sotienchiTT = GetSoTienChi(dtDoanhThuTT, lstMucDoanhThu[i]);
                    long sotienchiCK = GetSoTienChi(dtDoanhThuCK, lstMucDoanhThu[i]);
                    
                    row2["SOTIENTT"] = string.Format("{0:#,##0;Nothing}", sotienchiTT);
                    row2["SOTIENCK"] = string.Format("{0:#,##0;Nothing}", sotienchiCK);
                    row2["TONGTIENTTCK"] = string.Format("{0:#,##0;Nothing}", sotienchiTT + sotienchiCK);
                    
                }

                DataView objTimeSheetDV = new System.Data.DataView(dataTable);
                objTimeSheetDV.AllowNew = false;
                dtgvDanhSachHienThi.AutoGenerateColumns = false;
                dtgvDanhSachHienThi.DataSource = objTimeSheetDV;

                DataGridViewTextBoxColumn objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "STT",
                    DataPropertyName = "STT",
                    Name = "STT",
                    Width = 30
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "Mức doanh thu",
                    DataPropertyName = "MUCDOANHTHU",
                    Name = "MUCDOANHTHU",
                    Width = 120
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "SLKHTT",
                    DataPropertyName = "SLKHTT",
                    Name = "SLKHTT",
                    Width = 60
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "SLKHCK",
                    DataPropertyName = "SLKHCK",
                    Name = "SLKHCK",
                    Width = 60
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "Tổng doanh thu TT/CK",
                    DataPropertyName = "TONGDOANHTHUTTCK",
                    Name = "TONGDOANHTHUTTCK",
                    Width = 90
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "Số tiền trích thưởng",
                    DataPropertyName = "SOTIENTT",
                    Name = "SOTIENTT",
                    Width = 90
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "Số tiền chiết khấu",
                    DataPropertyName = "SOTIENCK",
                    Name = "SOTIENCK",
                    Width = 90
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "Tổng Chi TTCK",
                    DataPropertyName = "TONGTIENTTCK",
                    Name = "TONGTIENTTCK",
                    Width = 90
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "Tổng doanh thu tháng",
                    DataPropertyName = "TONGDOANHTHUTHANG",
                    Name = "TONGDOANHTHUTHANG",
                    Width = 90,
                    Visible = false
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                objColumn = new DataGridViewTextBoxColumn
                {
                    HeaderText = "Ghi chú",
                    DataPropertyName = "GHICHU",
                    Name = "GHICHU",
                    Width = 90,
                    Visible = false
                };
                dtgvDanhSachHienThi.Columns.Add(objColumn);

                dtgvDanhSachHienThi.ClearSelection();
                btnLapBaoCao.Enabled = true;
            }
        }

        private void WaitLoad()
        {
            waiting = new frmWaiting("Đang lấy dữ liệu");
            waiting.ShowDialog();
        }

        public void InitBaoCaoDuLieuNgayThang(Sheet sheet1)
        {
            Row createRow = sheet1.GetRow(6);
            createRow.GetCell(0).SetCellValue("Tháng " + thang.ToString() + " năm " + nam.ToString());
        }

        public void LapBaoCaoDuLieu(Sheet sheet1)
        {
            int row = 14;
            DataFormat format = hssfworkbook.CreateDataFormat();

            Font font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 10;

            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            numberStyle.DataFormat = format.GetFormat("#,#0");
            numberStyle.Alignment = HorizontalAlignment.RIGHT;
            numberStyle.BorderBottom = CellBorderType.THIN;
            numberStyle.BorderLeft = CellBorderType.THIN;
            numberStyle.BorderRight = CellBorderType.THIN;
            numberStyle.BorderTop = CellBorderType.THIN;
            numberStyle.VerticalAlignment = VerticalAlignment.CENTER;
            numberStyle.SetFont(font);

            CellStyle sttStyle = hssfworkbook.CreateCellStyle();
            sttStyle.DataFormat = format.GetFormat("#,#0");
            sttStyle.BorderBottom = CellBorderType.THIN;
            sttStyle.BorderLeft = CellBorderType.THIN;
            sttStyle.BorderRight = CellBorderType.THIN;
            sttStyle.BorderTop = CellBorderType.THIN;
            sttStyle.Alignment = HorizontalAlignment.CENTER;
            sttStyle.VerticalAlignment = VerticalAlignment.CENTER;
            sttStyle.SetFont(font);

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;
            textStyle.SetFont(font);
            
            for (int i = 0; i < dtgvDanhSachHienThi.Rows.Count; i++)
            {
                Row createRow = sheet1.CreateRow(row);
                createRow.Height = 400;

                Cell cell0 = createRow.CreateCell(0);
                cell0.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["STT"].Value.ToString()));
                cell0.CellStyle = sttStyle;

                Cell cell1 = createRow.CreateCell(1);
                cell1.SetCellValue(dtgvDanhSachHienThi.Rows[i].Cells["MUCDOANHTHU"].Value.ToString());
                cell1.CellStyle = textStyle;

                Cell cell2 = createRow.CreateCell(2);
                cell2.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["SLKHTT"].Value.ToString()));
                cell2.CellStyle = textStyle;

                Cell cell3 = createRow.CreateCell(3);
                cell3.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["SLKHCK"].Value.ToString()));
                cell3.CellStyle = numberStyle;

                Cell cell4 = createRow.CreateCell(4);
                cell4.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["TONGDOANHTHUTTCK"].Value.ToString()));
                cell4.CellStyle = numberStyle;

                Cell cell5 = createRow.CreateCell(5);
                cell5.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["SOTIENTT"].Value.ToString()));
                cell5.CellStyle = numberStyle;

                Cell cell6 = createRow.CreateCell(6);
                cell6.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["SOTIENCK"].Value.ToString()));
                cell6.CellStyle = numberStyle;

                Cell cell7 = createRow.CreateCell(7);
                cell7.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["TONGTIENTTCK"].Value.ToString()));
                cell7.CellStyle = numberStyle;

                Cell cell8 = createRow.CreateCell(8);
                cell8.SetCellValue(double.Parse(dtgvDanhSachHienThi.Rows[i].Cells["TONGDOANHTHUTHANG"].Value.ToString() == "" ? "0" : dtgvDanhSachHienThi.Rows[i].Cells["TONGDOANHTHUTHANG"].Value.ToString()));
                cell8.CellStyle = numberStyle;

                Cell cell9 = createRow.CreateCell(9);
                cell9.SetCellValue(dtgvDanhSachHienThi.Rows[i].Cells["GHICHU"].Value.ToString());
                cell9.CellStyle = textStyle;

                row++;
            }

            LapBaoCaoDuLieuTongCong(sheet1, row);
            LapBaoCaoDuLieuFooter(sheet1, row);
        }

        public void LapBaoCaoDuLieuFooter(Sheet sheet1, int row)
        {
            DataFormat format = hssfworkbook.CreateDataFormat();
            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            Font font1 = hssfworkbook.CreateFont();
            font1.Boldweight = (short)FontBoldWeight.BOLD;
            font1.FontHeightInPoints = 10;

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.SetFont(font1);
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.VerticalAlignment = VerticalAlignment.TOP;

            Row createRow = sheet1.CreateRow(row + 3);
            Cell cell = createRow.CreateCell(0);
            cell.SetCellValue("Ghi chú");

            createRow = sheet1.CreateRow(row + 4);
            cell = createRow.CreateCell(0);
            cell.SetCellValue("(2).Tổng doanh thu được trích thưởng/chiết khấu cho khách hàng là tổng doanh thu phát sinh được sử dụng lám căn cứ trích thưởng/chiết khấu theo quy định.");

            createRow = sheet1.CreateRow(row + 5);
            cell = createRow.CreateCell(0);
            cell.SetCellValue("(3). Tổng doanh thu tháng là tổng doanh thu phát sinh tất cả các dịch vụ Bưu chính chuyển phát của toàn Đơn vị trong tháng báo cáo.");

            createRow = sheet1.CreateRow(row + 8);
            cell = createRow.CreateCell(1);
            cell.SetCellValue("Người lập biểu");
            cell.CellStyle = textStyle;

            cell = createRow.CreateCell(7);
            cell.SetCellValue("Trưởng đơn vị");
            cell.CellStyle = textStyle;

            Row createRow2 = sheet1.CreateRow(row + 9);
            cell = createRow2.CreateCell(1);
            cell.SetCellValue("( ký tên ghi họ tên )");
            cell.CellStyle = textStyle;
        }


        public void LapBaoCaoDuLieuTongCong(Sheet sheet1, int row)
        {
            DataFormat format = hssfworkbook.CreateDataFormat();
            CellStyle numberStyle = hssfworkbook.CreateCellStyle();
            Font font1 = hssfworkbook.CreateFont();
            font1.Color = HSSFColor.RED.index;
            font1.Boldweight = (short)FontBoldWeight.BOLD;
            font1.FontHeightInPoints = 10;

            Font font2 = hssfworkbook.CreateFont();
            font2.Boldweight = (short)FontBoldWeight.BOLD;
            font2.FontHeightInPoints = 10;

            numberStyle.DataFormat = format.GetFormat("#,#0");
            numberStyle.Alignment = HorizontalAlignment.RIGHT;
            numberStyle.BorderBottom = CellBorderType.THIN;
            numberStyle.BorderLeft = CellBorderType.THIN;
            numberStyle.BorderRight = CellBorderType.THIN;
            numberStyle.BorderTop = CellBorderType.THIN;
            numberStyle.SetFont(font1);
            numberStyle.VerticalAlignment = VerticalAlignment.CENTER;

            CellStyle textStyle = hssfworkbook.CreateCellStyle();
            textStyle.BorderBottom = CellBorderType.THIN;
            textStyle.BorderLeft = CellBorderType.THIN;
            textStyle.BorderRight = CellBorderType.THIN;
            textStyle.BorderTop = CellBorderType.THIN;
            textStyle.Alignment = HorizontalAlignment.CENTER;
            textStyle.SetFont(font2);
            textStyle.VerticalAlignment = VerticalAlignment.CENTER;

            Row createRow = sheet1.CreateRow(row);
            createRow.Height = 400;

            Cell cell0 = createRow.CreateCell(0);
            cell0.SetCellValue("Tổng cộng");
            cell0.CellStyle = textStyle;

            Cell cell1 = createRow.CreateCell(1);
            cell1.CellStyle = textStyle;

            string formula = "SUM(C14:C" + row.ToString() + ")";
            Cell cell2 = createRow.CreateCell(2);
            cell2.CellFormula = formula;
            cell2.CellStyle = numberStyle;

            formula = "SUM(D14:D" + row.ToString() + ")";
            Cell cell3 = createRow.CreateCell(3);
            cell3.CellFormula = formula;
            cell3.CellStyle = numberStyle;

            formula = "SUM(E14:E" + row.ToString() + ")";
            Cell cell4 = createRow.CreateCell(4);
            cell4.CellFormula = formula;
            cell4.CellStyle = numberStyle;

            formula = "SUM(F14:F" + row.ToString() + ")";
            Cell cell5 = createRow.CreateCell(5);
            cell5.CellFormula = formula;
            cell5.CellStyle = numberStyle;

            formula = "SUM(G14:G" + row.ToString() + ")";
            Cell cell6 = createRow.CreateCell(6);
            cell6.CellFormula = formula;
            cell6.CellStyle = numberStyle;

            formula = "SUM(H14:H" + row.ToString() + ")";
            Cell cell7 = createRow.CreateCell(7);
            cell7.CellFormula = formula;
            cell7.CellStyle = numberStyle;

            formula = "SUM(I14:I" + row.ToString() + ")";
            Cell cell8 = createRow.CreateCell(8);
            cell8.CellFormula = formula;
            cell8.CellStyle = numberStyle;

            cell8 = createRow.CreateCell(9);
            cell8.CellStyle = textStyle;

            CellRangeAddress region = new CellRangeAddress(row, row, 0, 1);
            sheet1.AddMergedRegion(region);
        }

        private void btnLapBaoCao_Click(object sender, EventArgs e)
        {
            var ASynInvoke = new TimeTask(WaitLoad);
            IAsyncResult result = ASynInvoke.BeginInvoke(null, null);

            InitializeWorkbook();
            //Create sheet donvi
            Sheet sheet1 = hssfworkbook.GetSheet("Sheet1");
            if (sheet1 != null)
            {
                InitBaoCaoDuLieuNgayThang(sheet1);
                LapBaoCaoDuLieu(sheet1);

                WriteToFile();
                System.Diagnostics.Process.Start(Application.StartupPath + @"/Exports/HDTT1_" + maDonVi + "_" + thang.ToString() + "_" + nam.ToString() + ".xls");

                waiting.Close();
                waiting.Dispose();
                waiting = null;

                ASynInvoke.EndInvoke(result);
            }
        }

        public void WriteToFile()
        {
            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(Application.StartupPath + @"/Exports/HDTT1_" + maDonVi + "_" + thang.ToString() + "_" + nam.ToString() + ".xls", FileMode.Create);
            try
            {
                waiting.Hide();
                hssfworkbook.Write(file);
                MessageBox.Show("Lập báo cáo thành công", "Thong bao",
                       MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch
            {
                waiting.Hide();
                MessageBox.Show("Lập báo cáo thất bại", "Bao Loi",
                       MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            file.Close();
        }

        public void InitializeWorkbook()
        {
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
            FileStream file = new FileStream(Application.StartupPath + @"/ExportTemplates/HDTT_BIEU1.xls", FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            //create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            //create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;
        }
    }
}
